package com.teamsun.rptsql;

import com.teamsun.rptcfg.*;
import com.teamsun.util.CommonUtils;
import com.teamsun.util.Constants;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * <p>动态拼接SQL语句</p>
 * <p>该抽象类中拼接基础SQL,不同的数据库类型均可使用</p>
 * <p>个性化的SQL在该类的子类中实现,目前需要个性化拼接的SQL是分页查询SQL</p>
 */
public abstract class AbstraceConcateSQL {

    /**
     * 抽象方法
     * 根据不同的数据库类型拼接分页查询SQL
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param startIndex 分页查询起始行
     * @param endIndex 分页查询结束行
     * @return
     */
    public abstract String concatePageSQL(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex);

    /**
     * 抽象方法
     * 根据不同的数据库类型拼接分页查询SQL(非CUBE数据)
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param startIndex 分页查询起始行
     * @param endIndex 分页查询结束行
     * @return
     */
    public abstract String concateCubePageSQL(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex);

    /**
     * 拼接普通查询SQL(只是根据配置的column拼接SQL)
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param dbFunction 数据库个性化函数
     * @return
     */
    public String concateSelectSQL(BaseRpt baseRpt, Map<String, String> params, IDBFunction dbFunction) {
        // 报表配置中显示列信息
        List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
        // 报表配置中隐藏列信息(配置isShow=1的列)
        List<BaseRptColumn> hiddenRptColumns = baseRpt.getHiddenRptColumns();
        // 查询条件的信息
        Map<String, String> conditionMap = new HashMap<String, String>();

        String querySQL = "SELECT ";
        // 拼接SELECT语句
        for(BaseRptColumn rptColumn : rptColumns) {
            String field = rptColumn.getField();  // 数据库查询字段
            String aliasField = rptColumn.getAliasField();  // 查询字段别名
            if(Constants.YES.equals(rptColumn.getIsDateType())) {
                field = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
            }
            querySQL = querySQL + field + " AS \"" + aliasField + "\",";
        }

        // 拼接SELECT语句(isShow=1的列)
        for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {
            String field = hiddenRptColumn.getField();  // 数据库查询字段
            String aliasField = hiddenRptColumn.getAliasField();  // 查询字段别名
            String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
            if(Constants.YES.equals(hiddenRptColumn.getIsDateType())) {
                field = dbFunction.dateToCharFunction(field, hiddenRptColumn.getFormatter());
            }
            if(!Constants.NO.equals(isQueryColumn)) {
                querySQL = querySQL + field + " AS \"" + aliasField + "\",";
            }
        }
        if(querySQL.endsWith(",")) {
            querySQL = querySQL.substring(0, querySQL.length() - 1);
        }

        // 拼接from
        String fromInfo = baseRpt.getFromInfo();

        // 报表默认分组
        Map<String, DefaultGroup> groupMap = baseRpt.getDefaultGroup();

        // 报表存在嵌套SQL
        NestFrom nestFrom = baseRpt.getNestFrom();
        String nestSQL = nestFrom.getNestSQL();
        String nestFromSQL = "";
        List<InnerNestFrom> innerNestFroms = nestFrom.getInnerNestFromList();
        if(StringUtils.isNotBlank(nestSQL) || (innerNestFroms != null && innerNestFroms.size() > 0)) {
            if(StringUtils.isNotBlank(nestSQL)) {
                // 拼接查询条件
                Map<String, String> whereMap = concateWhereSQL(baseRpt, params, Constants.CONDITION_LVL_INNER, dbFunction);
                String whereSQL = whereMap.get(Constants.WHEREKEY);
                whereMap.remove(Constants.WHEREKEY);
                conditionMap.putAll(whereMap);
                nestFromSQL = nestSQL + whereSQL;

                DefaultGroup defaultGroup = groupMap.get(Constants.CONDITION_LVL_INNER);
                // 拼接嵌套SQL中的默认分组
                if(defaultGroup != null) {
                    if(Constants.CONDITION_LVL_INNER.equals(defaultGroup.getConditionLevel())) {
                        if(StringUtils.isNotBlank(defaultGroup.getGroupTxt())) {
                            nestFromSQL = nestFromSQL + " GROUP BY " + defaultGroup.getGroupTxt();
                        }
                    }
                }
            } else {
                // 拼接多级嵌套SQL语句
                for(int i= innerNestFroms.size()-1; i>=0; i--) {
                    InnerNestFrom innerNestFrom = innerNestFroms.get(i);
                    String innerLevel = innerNestFrom.getInnerLevel();
                    String innerSQL = innerNestFrom.getInnerSQL();
                    Map<String, String> whereMap = concateWhereSQL(baseRpt, params, innerLevel, dbFunction);
                    String whereSQL = whereMap.get(Constants.WHEREKEY);
                    whereMap.remove(Constants.WHEREKEY);
                    conditionMap.putAll(whereMap);

                    if(i == innerNestFroms.size() - 1) {
                        nestFromSQL = innerSQL + whereSQL;
                    } else {
                        nestFromSQL = innerSQL + " FROM ( " + nestFromSQL + " ) ";
                        nestFromSQL = nestFromSQL + whereSQL;
                    }

                    DefaultGroup defaultGroup = groupMap.get(innerLevel);
                    // 拼接默认分组
                    if(defaultGroup != null) {
                        if(innerLevel.equals(defaultGroup.getConditionLevel())) {
                            if(StringUtils.isNotBlank(defaultGroup.getGroupTxt())) {
                                nestFromSQL = nestFromSQL + " GROUP BY " + defaultGroup.getGroupTxt();
                            }
                        }
                    }
                }
            }

            if(StringUtils.isNotBlank(fromInfo)) {
                fromInfo = "(" + nestFromSQL + ") a " + fromInfo;
            } else {
                fromInfo = "(" + nestFromSQL + ") a ";
            }
        }

        querySQL = querySQL + " FROM " + fromInfo;

        // 拼接查询条件
        Map<String, String> whereMap = concateWhereSQL(baseRpt, params, Constants.CONDITION_LVL_MID, dbFunction);
        String whereSQL = whereMap.get(Constants.WHEREKEY);
        whereMap.remove(Constants.WHEREKEY);
        conditionMap.putAll(whereMap);
        querySQL = querySQL + whereSQL;

        // 拼接默认group by
        DefaultGroup defaultGroup = groupMap.get(Constants.CONDITION_LVL_MID);
        if(defaultGroup != null) {
            String conditionLvl = defaultGroup.getConditionLevel();
            if(Constants.CONDITION_LVL_MID.equals(conditionLvl) || StringUtils.isBlank(conditionLvl)) {
                if(StringUtils.isNotBlank(defaultGroup.getGroupTxt())) {
                    querySQL = querySQL + " GROUP BY " + defaultGroup.getGroupTxt();
                }
            }
        }

        // 拼接order by
        String orderBy = baseRpt.getOrderInfo();

        // 如果前台指定排序方式,则按照指定排序方式查询
        String order = params.get(Constants.KEY_ORDER);
        String sort = params.get(Constants.KEY_SORT);
        if(StringUtils.isNotBlank(sort)) {
            querySQL = querySQL + " ORDER BY \"" + sort + "\" " + order;
        } else {  // 如果前台未指定排序方式,则按照配置的默认排序方式查询
            if (StringUtils.isNotBlank(orderBy)) {
                querySQL = querySQL + " ORDER BY " + orderBy;
            }
        }

        if(conditionMap != null && conditionMap.size() > 0) {
            for(Map.Entry<String, String> entry : conditionMap.entrySet()) {
                String key = entry.getKey();
                String value = entry.getValue();
                key = "${" + key + "}";
                if(querySQL.contains(key)) {
                    querySQL = querySQL.replace(key, value);
                }
            }
        }
        return querySQL;
    }

    /**
     * 拼接非CUBE数据查询SQL(sum和group by需要动态拼接)
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param dbFunction 数据库个性化函数
     * @return
     */
    public String concateCubeSelectSQL(BaseRpt baseRpt, Map<String, String> params, IDBFunction dbFunction) {
        // 报表配置中显示列信息
        List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
        // 报表配置中隐藏列信息(isShow=1的列)
        List<BaseRptColumn> hiddenRptColumns = baseRpt.getHiddenRptColumns();
        // 获前台页面取选取的维度信息
        String selectDim = params.get(Constants.RPTDIM_KEY);
        List<String> cascadeList = new ArrayList<String>();
        List<String> queryColumn = new ArrayList<String>();
        // 查询条件信息
        Map<String, String> conditionMap = new HashMap<String, String>();

        String querySQL = "SELECT ";
        if(Constants.YES.equals(baseRpt.getAllIdx())) {
            // 如果报表没有维度，全是指标；则直接调用concateSelectSQL方法
            return concateSelectSQL(baseRpt, params, dbFunction);
        } else if(StringUtils.isBlank(selectDim)) {  // 如果未选择维度,则不需要group by
            for (BaseRptColumn rptColumn : rptColumns) {
                String field = rptColumn.getField();  // 数据库查询字段
                String columnField = field;
                String aliasField = rptColumn.getAliasField();  // 查询字段别名

                // 如果配置报表中包含sum()函数，则取消sum函数
                field = field.toLowerCase();
                if(Constants.YES.equals(rptColumn.getIsDateType())) {
                    field = dbFunction.dateToCharFunction(columnField, rptColumn.getFormatter());
                }
                if (Constants.YES.equals(rptColumn.getIsSumCol()) && !field.contains("sum(")) {  // 查询汇总字段
                    querySQL = querySQL + " SUM(" + columnField + ") AS \"" + aliasField + "\",";
                } else if(Constants.YES.equals(rptColumn.getIsCount()) && !field.contains("count(")) {
                    querySQL = querySQL + " COUNT(" + columnField + ") AS \"" + aliasField + "\",";
                } else {
                    if(Constants.NO.equals(rptColumn.getColType())) {  // 查询非汇总指标
                        if(field.contains("sum(") || field.contains("count(")) {
                            querySQL = querySQL + columnField + " AS \"" + aliasField + "\",";
                        }
                    }
                }
            }

            for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {  // 查询隐藏字段
                String field = hiddenRptColumn.getField();  // 数据库查询字段
                String columnField = field;
                String aliasField = hiddenRptColumn.getAliasField();  // 查询字段别名

                field = field.toLowerCase();
                if(Constants.YES.equals(hiddenRptColumn.getIsDateType())) {
                    field = dbFunction.dateToCharFunction(columnField, hiddenRptColumn.getFormatter());
                }
                String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
                if (!Constants.NO.equals(isQueryColumn)) {
                    if (Constants.YES.equals(hiddenRptColumn.getIsSumCol()) && !field.contains("sum(")) {  // 查询汇总字段
                        querySQL = querySQL + " SUM(" + columnField + ") AS \"" + aliasField + "\",";
                    } else if(Constants.YES.equals(hiddenRptColumn.getIsCount()) && !field.contains("count(")) {
                        querySQL = querySQL + " COUNT(" + columnField + ") AS \"" + aliasField + "\",";
                    } else {
                        if(Constants.NO.equals(hiddenRptColumn.getColType())) {  // 查询非汇总指标
                            if(field.contains("sum(") || field.contains("count(")) {
                                querySQL = querySQL + columnField + " AS \"" + aliasField + "\",";
                            }
                        }
                    }
                }
            }
        } else {  // 如果选择维度则需要根据选择的维度做group by 和 sum
            String[] selectDims = selectDim.split(",");
            List<String> dimList = Arrays.asList(selectDims);
            for (BaseRptColumn rptColumn : rptColumns) {
                String field = rptColumn.getField();  // 数据库查询字段
                String columnField = field;
                field = field.toLowerCase();
                String selectField = field;
                if(Constants.YES.equals(rptColumn.getIsDateType())) {
                    selectField = dbFunction.dateToCharFunction(columnField, rptColumn.getFormatter());
                }
                String aliasField = rptColumn.getAliasField();  // 查询字段别名
                if (Constants.YES.equals(rptColumn.getIsSumCol()) && !field.contains("sum(") && !field.contains("count(") && !field.contains("avg(")) {  // 查询汇总字段
                    querySQL = querySQL + " SUM(" + columnField + ") AS \"" + aliasField + "\",";
                    queryColumn.add("sum(" + selectField + ")");
                } else if(Constants.YES.equals(rptColumn.getIsCount()) && !field.contains("sum(") && !field.contains("count(") && !field.contains("avg(")) {
                    querySQL = querySQL + " COUNT(" + columnField + ") AS \"" + aliasField + "\",";
                    queryColumn.add("count(" + selectField + ")");
                } else {
                    if(Constants.NO.equals(rptColumn.getColType())) {  // 查询非汇总指标
                        if(field.contains("sum(") || field.contains("count(") || field.contains("avg(")) {
                            querySQL = querySQL + columnField + " AS \"" + aliasField + "\",";
                            queryColumn.add(selectField);
                        }
                    }

                    if(dimList.contains(aliasField)) {  // 查询维度字段
                        // 获取维度的关联维度(这种情况存在于以下应用场景:报表下钻时需要根据机构代码查询,但是报表
                        // 页面不需要展示机构代码只需展示机构名称,这时就需要配置机构名称的级联维度为机构代码)
                        String cascadeDim = rptColumn.getCascade();
                        if(StringUtils.isNotBlank(cascadeDim)) {
                            cascadeList.add(cascadeDim);
                        }

                        querySQL = querySQL + columnField + " AS \"" + aliasField + "\",";
                        queryColumn.add(field);
                    }
                }
            }

            for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {  // 查询隐藏字段
                String field = hiddenRptColumn.getField();  // 数据库查询字段
                String columnField = field;
                field = field.toLowerCase();
                String selectField = field;
                if(Constants.YES.equals(hiddenRptColumn.getIsDateType())) {
                    selectField = dbFunction.dateToCharFunction(columnField, hiddenRptColumn.getFormatter());
                }
                String aliasField = hiddenRptColumn.getAliasField();  // 查询字段别名
                String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
                if (!Constants.NO.equals(isQueryColumn)) {
                    if(Constants.YES.equals(hiddenRptColumn.getIsSumCol()) && !field.contains("sum(") && !field.contains("count(") && !field.contains("avg(")) {
                        querySQL = querySQL + " SUM(" + columnField + ") AS \"" + aliasField + "\",";
                        queryColumn.add("sum(" + selectField + ")");
                    } else if(Constants.YES.equals(hiddenRptColumn.getIsCount()) && !field.contains("sum(") && !field.contains("count(") && !field.contains("avg(")) {
                        querySQL = querySQL + " COUNT(" + columnField + ") AS \"" + aliasField + "\",";
                        queryColumn.add("count(" + selectField + ")");
                    } else {
                        if(Constants.NO.equals(hiddenRptColumn.getColType())) {  // 查询非汇总指标
                            if(field.contains("sum(") || field.contains("count(") || field.contains("avg(")) {
                                querySQL = querySQL + columnField + " AS \"" + aliasField + "\",";
                                queryColumn.add(selectField);
                            }
                        } else {
                            querySQL = querySQL + columnField + " AS \"" + aliasField + "\",";
                            queryColumn.add(field);
                        }
                    }
                }
            }

            // 拼接级联维度
            for (BaseRptColumn rptColumn : rptColumns) {
                String field = rptColumn.getField();  // 数据库查询字段
                String aliasField = rptColumn.getAliasField();  // 查询字段别名
                if(cascadeList.contains(aliasField)) {
                    String selectField = field;
                    if(Constants.YES.equals(rptColumn.getIsDateType())) {
                        selectField = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
                    }
                    querySQL = querySQL + selectField + " AS \"" + aliasField + "\",";
                    queryColumn.add(field);
                }
            }

            // 拼接级联维度
            for (BaseRptColumn hiddenRptColumn : hiddenRptColumns) {
                String field = hiddenRptColumn.getField();  // 数据库查询字段
                String aliasField = hiddenRptColumn.getAliasField();  // 查询字段别名
                if(cascadeList.contains(aliasField)) {

                    String selectField = field;
                    if(Constants.YES.equals(hiddenRptColumn.getIsDateType())) {
                        selectField = dbFunction.dateToCharFunction(field, hiddenRptColumn.getFormatter());
                    }
                    querySQL = querySQL + selectField + " AS \"" + aliasField + "\",";
                    queryColumn.add(field);
                }
            }
        }

        if(querySQL.endsWith(",")) {
            querySQL = querySQL.substring(0, querySQL.length() - 1);
        }

        // 拼接from
        String fromInfo = baseRpt.getFromInfo();

        // 报表初始化默认分组
        Map<String, DefaultGroup> groupMap = baseRpt.getDefaultGroup();
        // having过滤条件
        Map<String, Having> havingMap = baseRpt.getHaving();

        // 报表存在嵌套SQL
        NestFrom nestFrom = baseRpt.getNestFrom();
        String nestSQL = nestFrom.getNestSQL();
        String nestFromSQL = "";
        List<InnerNestFrom> innerNestFroms = nestFrom.getInnerNestFromList();
        if(StringUtils.isNotBlank(nestSQL) || (innerNestFroms != null && innerNestFroms.size() > 0)) {
            // 获取未选择的维度且需要和嵌套SQL级联的维度信息
            List<String> innerDims = new ArrayList<String>();
            if(StringUtils.isNotBlank(selectDim)) {
                List<BaseRptColumn> columns = baseRpt.getRptColumns();
                for (BaseRptColumn column : columns) {
                    if (Constants.YES.equals(column.getColType())) {
                        String aliasField = column.getAliasField();
                        String innerDim = column.getInnerDim();
                        if(!selectDim.contains(aliasField)) {
                            if(StringUtils.isNotBlank(innerDim)) {
                                innerDims.add(innerDim);
                            }
                        }
                    }
                }
            }

            Pattern pattern = Pattern.compile("\t|\r|\n");
            // 报表初始化默认分组
            if(StringUtils.isNotBlank(nestSQL)) {
                nestSQL = nestSQL.trim();
                Matcher nestMatcher = pattern.matcher(nestSQL);
                nestSQL = nestMatcher.replaceAll(" ");

                // 如果嵌套SQL的维度需要和外部维度级联
                if(innerDims.size() > 0) {
                    for(String innerDim : innerDims) {
                        nestSQL = nestSQL.replaceAll(innerDim + ",", "");
                        //nestSQL = nestSQL.replaceAll("," + innerDim, "");
                    }
                }

                nestSQL = nestSQL.trim();
                if(nestSQL.endsWith(",")) {
                    nestSQL = nestSQL.substring(0, nestSQL.length() - 1);
                }
                Map<String, String> whereMap = concateWhereSQL(baseRpt, params, Constants.CONDITION_LVL_INNER, dbFunction);
                String whereSQL = whereMap.get(Constants.WHEREKEY);
                whereMap.remove(Constants.WHEREKEY);
                conditionMap.putAll(whereMap);
                nestFromSQL = nestSQL + whereSQL;

                // 拼接默认分组
                DefaultGroup defaultGroup = groupMap.get(Constants.CONDITION_LVL_INNER);
                Having having = havingMap.get(Constants.CONDITION_LVL_INNER);
                if(defaultGroup != null) {
                    if(Constants.CONDITION_LVL_INNER.equals(defaultGroup.getConditionLevel())) {
                        String groupInfo = defaultGroup.getGroupTxt();
                        groupInfo = groupInfo.trim();
                        Matcher groupMatcher = pattern.matcher(groupInfo);
                        groupInfo = groupMatcher.replaceAll(" ");
                        if(innerDims.size() > 0) {
                            for(String innerDim : innerDims) {
                                groupInfo = groupInfo.replaceAll(innerDim + ",", "");
                                //groupInfo = groupInfo.replaceAll("," + innerDim, "");
                            }
                        }

                        groupInfo = groupInfo.trim();
                        if(groupInfo.endsWith(",")) {
                            groupInfo = groupInfo.substring(0, groupInfo.length() - 1);
                        }
                        if(StringUtils.isNotBlank(groupInfo)) {
                            nestFromSQL = nestFromSQL + " GROUP BY " + groupInfo;
                            if(having != null) {
                                String havingInfo = having.getHavingTxt();
                                if(StringUtils.isNotBlank(havingInfo)) {
                                    nestFromSQL = nestFromSQL + " HAVING " + havingInfo;
                                }
                            }
                        }
                    }
                }
            } else {
                // 拼接多级嵌套SQL语句
                for(int i= innerNestFroms.size()-1; i>=0; i--) {
                    InnerNestFrom innerNestFrom = innerNestFroms.get(i);
                    String innerLevel = innerNestFrom.getInnerLevel();
                    String innerSQL = innerNestFrom.getInnerSQL();
                    Map<String, String> whereMap = concateWhereSQL(baseRpt, params, innerLevel, dbFunction);
                    String whereSQL = whereMap.get(Constants.WHEREKEY);
                    whereMap.remove(Constants.WHEREKEY);
                    conditionMap.putAll(whereMap);

                    // 如果嵌套SQL的维度需要和外部维度级联
                    innerSQL = innerSQL.trim();
                    Matcher innerMatcher = pattern.matcher(innerSQL);
                    innerSQL = innerMatcher.replaceAll(" ");
                    if(innerDims.size() > 0) {
                        for(String innerDim : innerDims) {
                            innerSQL = innerSQL.replaceAll(innerDim + ",", "");
                            //innerSQL = innerSQL.replaceAll("," + innerDim, "");
                        }
                    }
                    innerSQL = innerSQL.trim();
                    if(innerSQL.endsWith(",")) {
                        innerSQL = innerSQL.substring(0, innerSQL.length() - 1);
                    }

                    if(i == innerNestFroms.size() - 1) {
                        nestFromSQL = innerSQL + whereSQL;
                    } else {
                        nestFromSQL = innerSQL + " FROM ( " + nestFromSQL + " ) ";
                        nestFromSQL = nestFromSQL + whereSQL;
                    }

                    // 拼接默认分组
                    DefaultGroup defaultGroup = groupMap.get(innerLevel);
                    Having having = havingMap.get(innerLevel);
                    if(defaultGroup != null) {
                        if(innerLevel.equals(defaultGroup.getConditionLevel())) {
                            String groupInfo = defaultGroup.getGroupTxt();
                            groupInfo = groupInfo.trim();
                            Matcher groupMatcher = pattern.matcher(groupInfo);
                            groupInfo = groupMatcher.replaceAll(" ");
                            if(innerDims.size() > 0) {
                                for(String innerDim : innerDims) {
                                    groupInfo = groupInfo.replaceAll(innerDim + ",", "");
                                    //groupInfo = groupInfo.replaceAll("," + innerDim, "");
                                }
                            }
                            groupInfo = groupInfo.trim();
                            if(groupInfo.endsWith(",")) {
                                groupInfo = groupInfo.substring(0, groupInfo.length() - 1);
                            }
                            if(StringUtils.isNotBlank(groupInfo)) {
                                nestFromSQL = nestFromSQL + " GROUP BY " + groupInfo;
                                if(having != null) {
                                    String havingInfo = having.getHavingTxt();
                                    if(StringUtils.isNotBlank(havingInfo)) {
                                        nestFromSQL = nestFromSQL + " HAVING " + havingInfo;
                                    }
                                }
                            }
                        }
                    }
                }
            }

            if(StringUtils.isNotBlank(fromInfo)) {
                fromInfo = "(" + nestFromSQL + ") a " + fromInfo;
            } else {
                fromInfo = "(" + nestFromSQL + ") a ";
            }
        }

        querySQL = querySQL + " FROM " + fromInfo;

        // 拼接查询条件
        Map<String, String> whereMap = concateWhereSQL(baseRpt, params, Constants.CONDITION_LVL_MID, dbFunction);
        String whereSQL = whereMap.get(Constants.WHEREKEY);
        whereMap.remove(Constants.WHEREKEY);
        conditionMap.putAll(whereMap);
        querySQL = querySQL + whereSQL;

        // 拼接group by
        String groupBySQL = "";
        Having having = havingMap.get(Constants.CONDITION_LVL_MID);
        if(StringUtils.isNotBlank(selectDim)) {  // 如果选择维度
            String[] selectDims = selectDim.split(",");
            List<String> dimList = Arrays.asList(selectDims);

            for(BaseRptColumn rptColumn : rptColumns) {
                String field = rptColumn.getField();
                String aliasField = rptColumn.getAliasField();
                if(dimList.contains(aliasField) || cascadeList.contains(aliasField)) {
                    groupBySQL = groupBySQL + field + ",";
                }
            }

            for(BaseRptColumn rptColumn : hiddenRptColumns) {
                String field = rptColumn.getField();
                String aliasField = rptColumn.getAliasField();
                if(cascadeList.contains(aliasField)) {
                    groupBySQL = groupBySQL + field + ",";
                }
            }

            // 默认拼接隐藏维度(colType=1 isShow=0)
            for(BaseRptColumn rptColumn : hiddenRptColumns) {
                String colType = rptColumn.getColType();
                String isQueryColumn = rptColumn.getIsQueryColumn();
                if(Constants.YES.equals(colType) && !Constants.NO.equals(isQueryColumn)) {
                    String field = rptColumn.getField();
                    groupBySQL = groupBySQL + field + ",";
                }
            }

            if(groupBySQL.endsWith(",")) {
                groupBySQL = groupBySQL.substring(0, groupBySQL.length() - 1);
            }
            querySQL = querySQL + " GROUP BY " + groupBySQL;
            if(having != null) {
                String havingInfo = having.getHavingTxt();
                querySQL = querySQL + " HAVING " + havingInfo;
            }
        } else {
            DefaultGroup defaultGroup = groupMap.get(Constants.CONDITION_LVL_MID);
            // 拼接默认分组
            if(defaultGroup != null) {
                String conditionLvl = defaultGroup.getConditionLevel();
                if(Constants.CONDITION_LVL_MID.equals(conditionLvl) || StringUtils.isBlank(conditionLvl)) {
                    if(StringUtils.isNotBlank(defaultGroup.getGroupTxt())) {
                        querySQL = querySQL + " GROUP BY " + defaultGroup.getGroupTxt();
                        if(having != null) {
                            String havingInfo = having.getHavingTxt();
                            if(StringUtils.isNotBlank(havingInfo)) {
                                querySQL = querySQL + " HAVING " + havingInfo;
                            }
                        }
                    }
                }
            }
        }

        // 拼接order by
        String orderBy = baseRpt.getOrderInfo();

        // 如果前台指定排序方式,则按照指定排序方式查询
        String order = params.get(Constants.KEY_ORDER);
        String sort = params.get(Constants.KEY_SORT);
        if(StringUtils.isNotBlank(sort)) {
            querySQL = querySQL + " ORDER BY \"" + sort + "\" " + order;
        } else {  // 如果前台未指定排序方式,则按照配置的默认排序方式查询
            if (StringUtils.isNotBlank(orderBy)) {
                orderBy = orderBy.toLowerCase();
                String orderInfo = "";
                String sortInfo = "";
                if(orderBy.endsWith("desc")) {
                    orderInfo = "desc";
                    orderBy = orderBy.replace("desc", "");
                    orderBy = orderBy.trim();
                    String[] orders = orderBy.split(",");
                    for(String ord : orders) {
                        if(queryColumn.contains(ord)) {
                            sortInfo = sortInfo + ord + ",";
                        }
                    }
                } else {
                    orderInfo = "asc";
                    if(orderBy.endsWith("asc")) {
                        orderBy = orderBy.replace("acs", "");
                    }
                    orderBy = orderBy.trim();
                    String[] orders = orderBy.split(",");
                    for(String ord : orders) {
                        if(queryColumn.contains(ord)) {
                            sortInfo = sortInfo + ord + ",";
                        }
                    }
                }
                if(sortInfo.endsWith(",")) {
                    sortInfo = sortInfo.substring(0, sortInfo.length() - 1);
                }
                if(StringUtils.isNotBlank(sortInfo)) {
                    sortInfo = sortInfo + " " + orderInfo;
                    querySQL = querySQL + " ORDER BY " + sortInfo;
                }
            }
        }

        // 拼接外层SQL查询条件
        String outerWhereSQL = concateOuterWhereSQL(baseRpt, params, Constants.CONDITION_LVL_OUTER, dbFunction);
        if(StringUtils.isNotBlank(outerWhereSQL)) {
            querySQL = "SELECT * FROM ( " + querySQL + " ) " + outerWhereSQL;
        }

        if(conditionMap != null && conditionMap.size() > 0) {
            for(Map.Entry<String, String> entry : conditionMap.entrySet()) {
                String key = entry.getKey();
                String value = entry.getValue();
                key = "${" + key + "}";
                if(querySQL.contains(key)) {
                    querySQL = querySQL.replace(key, value);
                }
            }
        }
        return querySQL;
    }

    /**
     * 拼接汇总查询SQL
     * @param baseRpt 报表配置信息
     * @return
     */
    public String concateSumSQL(BaseRpt baseRpt, String querySQL) {
        String sumSQL = "SELECT ";

        // 报表配置中显示列信息
        List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
        // 报表配置中隐藏列信息
        List<BaseRptColumn> hiddenRptColumns = baseRpt.getHiddenRptColumns();

        List<BaseRptColumn> allColumns = new ArrayList<BaseRptColumn>();
        allColumns.addAll(rptColumns);
        allColumns.addAll(hiddenRptColumns);

        int colCount = 0;
        for(BaseRptColumn rptColumn : rptColumns) {
            String aliasField = rptColumn.getAliasField();
            String field = rptColumn.getField();
            field = field.toLowerCase();

            // 该列是否为汇总列(0-否  1-是)
            String isSumCol = rptColumn.getIsSumCol();
            // 该列是否为计数列(0-否  1-是)
            String isCount = rptColumn.getIsCount();

            if(Constants.YES.equals(isSumCol)) {
                if(field.contains("sum(")) {
                    for(BaseRptColumn column : allColumns) {
                        String alias = column.getAliasField();
                        alias = alias.toLowerCase();
                        String compareAlias = "sum(" + alias + ")";

                        if(field.contains(compareAlias)) {
                            String replaceAlias = "sum(\"" + alias + "\")";
                            field = field.replace(compareAlias, replaceAlias);
                        }
                    }
                    sumSQL = sumSQL + field + " AS \"" + aliasField + "\",";
                } else {
                    // 如果查询列含有四则运算符
                    if(field.contains("+") || field.contains("-") || field.contains("*") || field.contains("/")) {
                        String resetField = CommonUtils.exactMatch(allColumns, field);
                        sumSQL = sumSQL + resetField + " AS \"" + aliasField + "\",";
                    } else {
                        sumSQL = sumSQL + "SUM(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                    }
                }
            } else if(Constants.YES.equals(isCount)) {
                sumSQL = sumSQL + "SUM(\"" + aliasField + "\") AS \"" + aliasField + "\",";
            } else if(field.contains("avg(")){
                sumSQL = sumSQL + "AVG(\"" + aliasField + "\") AS \"" + aliasField + "\",";
            } else {
                if(colCount == 0) {
                    sumSQL = sumSQL + "'汇总' AS \"" + aliasField + "\",";
                } else {
                    sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
                }
            }
            colCount ++;
        }

        for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {
            String aliasField = hiddenRptColumn.getAliasField();
            String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
            if (!Constants.NO.equals(isQueryColumn)) {
                sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
            }
        }

        if(sumSQL.endsWith(",")) {
            sumSQL = sumSQL.substring(0, sumSQL.length() - 1);
        }

        sumSQL = sumSQL + " FROM (" + querySQL + ") t";
        return sumSQL;
    }

    /**
     * 拼接汇总SQL(sum和group by需要动态拼接)
     * @param baseRpt
     * @param params
     * @return
     */
    public String concateCubeSumSQL(BaseRpt baseRpt, Map<String, String> params, String querySQL) {
        String selectDim = params.get(Constants.RPTDIM_KEY);  // 选择的维度
        String sumSQL = "SELECT ";

        // 报表配置中显示列信息
        List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
        // 报表配置中隐藏列信息
        List<BaseRptColumn> hiddenRptColumns = baseRpt.getHiddenRptColumns();

        List<BaseRptColumn> allColumns = new ArrayList<BaseRptColumn>();
        allColumns.addAll(rptColumns);
        allColumns.addAll(hiddenRptColumns);

        if(StringUtils.isBlank(selectDim)) {  // 未选择维度
            int colCount = 0;
            for(BaseRptColumn rptColumn : rptColumns) {
                String aliasField = rptColumn.getAliasField();
                String field = rptColumn.getField();
                field = field.toLowerCase();

                // 该列是否为汇总列(0-否  1-是)
                String isSumCol = rptColumn.getIsSumCol();
                // 该列是否为计数列(0-否  1-是)
                String isCount = rptColumn.getIsCount();

                if(Constants.YES.equals(isSumCol)) {
                    if(field.contains("sum(")) {
                        for(BaseRptColumn column : allColumns) {
                            String alias = column.getAliasField();
                            alias = alias.toLowerCase();
                            String compareAlias = "sum(" + alias + ")";

                            if(field.contains(compareAlias)) {
                                String replaceAlias = "sum(\"" + alias + "\")";
                                field = field.replace(compareAlias, replaceAlias);
                            }
                        }
                        sumSQL = sumSQL + field + " AS \"" + aliasField + "\",";
                    } else {
                        // 如果查询列含有四则运算符
                        if(field.contains("+") || field.contains("-") || field.contains("*") || field.contains("/")) {
                            String resetField = CommonUtils.exactMatch(allColumns, field);
                            sumSQL = sumSQL + resetField + " AS \"" + aliasField + "\",";;
                        } else {
                            sumSQL = sumSQL + "SUM(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                        }
                    }
                } else if(Constants.YES.equals(isCount)) {
                    sumSQL = sumSQL + "SUM(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                } else if(field.contains("avg(")){
                    sumSQL = sumSQL + "AVG(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                } else {
                    if(colCount == 0) {
                        sumSQL = sumSQL + "'汇总' AS \"" + aliasField + "\",";
                    } else {
                        sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
                    }
                }
                colCount ++;
            }
        } else {  // 选择维度
            int colCount = 0;
            String[] selectDims = selectDim.split(",");
            List<String> dimList = Arrays.asList(selectDims);
            for(BaseRptColumn rptColumn : rptColumns) {
                String aliasField = rptColumn.getAliasField();
                String field = rptColumn.getField();
                field = field.toLowerCase();
                if(Constants.YES.equals(rptColumn.getIsSumCol())) {
                    if(field.contains("sum(")) {
                        for(BaseRptColumn column : allColumns) {
                            String alias = column.getAliasField();
                            alias = alias.toLowerCase();
                            String compareAlias = "sum(" + alias + ")";

                            if(field.contains(compareAlias)) {
                                String replaceAlias = "sum(\"" + alias + "\")";
                                field = field.replace(compareAlias, replaceAlias);
                            }
                        }
                        sumSQL = sumSQL + field + " AS \"" + aliasField + "\",";
                    } else {
                        if(field.contains("+") || field.contains("-") || field.contains("*") || field.contains("/")) {
                            String resetField = CommonUtils.exactMatch(allColumns, field);
                            sumSQL = sumSQL + resetField + " AS \"" + aliasField + "\",";
                        } else {
                            sumSQL = sumSQL + "SUM(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                        }
                    }
                } else if(Constants.YES.equals(rptColumn.getIsCount())) {
                    sumSQL = sumSQL + "SUM(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                } else if(field.contains("avg(")){
                    sumSQL = sumSQL + "AVG(\"" + aliasField + "\") AS \"" + aliasField + "\",";
                }  else {
                    if(dimList.contains(aliasField)) {
                        if(colCount == 0) {
                            sumSQL = sumSQL + "'汇总' AS \"" + aliasField + "\",";
                        } else {
                            sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
                        }
                        colCount ++;
                    }
                }
            }
        }

        for(BaseRptColumn hiddenRptColumn : hiddenRptColumns) {
            String aliasField = hiddenRptColumn.getAliasField();
            String isQueryColumn = hiddenRptColumn.getIsQueryColumn();  // 列是否是查询列
            if (!Constants.NO.equals(isQueryColumn)) {
                sumSQL = sumSQL + "'' AS \"" + aliasField + "\",";
            }
        }

        if(sumSQL.endsWith(",")) {
            sumSQL = sumSQL.substring(0, sumSQL.length() - 1);
        }

        sumSQL = sumSQL + " FROM (" + querySQL + ") t";
        return sumSQL;
    }

    /**
     * 查询数据总数
     * @param dataSource
     * @return
     */
    public String concateCountSQL(String dataSource) {
        String selectSQL = "SELECT COUNT(*) FROM (" + dataSource + ") t";
        return selectSQL;
    }

    /**
     * 拼接查询条件
     * @param baseRpt 报表信息
     * @param params 查询参数
     * @param dbFunction 数据库个性化函数
     * @return
     */
    private Map<String, String> concateWhereSQL(BaseRpt baseRpt, Map<String, String> params, String needLvl, IDBFunction dbFunction) {
        // 拼接查询条件
        String whereSQL = " WHERE 1=1 ";

        // 保存查询条件信息
        Map<String, String> conditionMap = new HashMap<String, String>();

        // OR查询条件((A AND B) OR (C AND D))
        List<String> orCondition = new ArrayList<String>();
        // OR查询条件((A OR B) AND (C OR D))
        List<String> orAndCondition = new ArrayList<String>();

        // 报表默认查询条件
        Map<String,DefaultWhere> whereMap = baseRpt.getDefaultWhere();
        DefaultWhere defaultWhere = whereMap.get(needLvl);
        if(defaultWhere != null) {
            String initWhere = defaultWhere.getConditionTxt();
            String andWhere = defaultWhere.getAndCondition();
            String orWhere = defaultWhere.getOrCondition();
            String conditionLvl = defaultWhere.getConditionLevel();

            if(StringUtils.isNotBlank(initWhere)) {
                if(isConditionLvl(conditionLvl, needLvl)) {
                    whereSQL = whereSQL + " AND " + initWhere;
                }
            }
            if(StringUtils.isNotBlank(andWhere)) {
                if(isConditionLvl(conditionLvl, needLvl)) {
                    whereSQL = whereSQL + " AND " + andWhere;
                }
            }
            if(StringUtils.isNotBlank(orWhere)) {
                if(isConditionLvl(conditionLvl, needLvl)) {
                    String conditionSQL = " AND " + orWhere;
                    orCondition.add(conditionSQL);
                }
            }
        }

        // 报表查询条件可分为页面传入的查询条件及页面跳转传入的初始化查询条件
        if(params != null && params.size() > 0) {
            List<BaseRptColumn> conditionCol = baseRpt.getConditionColumns();  // 报表查询列
            String initParam = baseRpt.getInitCondition();  // 报表初始化查询条件
            String[] initParams = initParam.split(",");
            // 查询条件来源于页面form表单
            if (conditionCol != null && conditionCol.size() > 0) {
                for (BaseRptColumn rptColumn : conditionCol) {  // 拼接查询列数据
                    String isSQLColumn = rptColumn.getIsSQLColumn();
                    String conditionLvl = rptColumn.getConditionLevel();

                    if(isConditionLvl(conditionLvl, needLvl)) {
                        String field = rptColumn.getField();
                        String aliasField = rptColumn.getAliasField();
                        String conditionType = rptColumn.getConditionType();  // 查询条件类型
                        String paramValue = params.get(aliasField);  // 参数值
                        String isOr = rptColumn.getIsOrOperator();  // 是否是OR查询
                        if (StringUtils.isNotBlank(paramValue) && !Constants.CONSTANTS_UNDEFINED.equalsIgnoreCase(paramValue)) {

                            if (Constants.CONDITIONTYPE_INPUT.equals(conditionType)) { // 如果查询类型为输入框，判断是否是模糊查询
                                if (Constants.YES.equals(rptColumn.getQueryType())) {
                                    // 精确查询
                                    conditionMap.put(aliasField, "\'" + paramValue + "\'");
                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        if(!field.contains(",") || (field.contains("(") && field.contains(")"))) {
                                            // 如果是OR查询则先保存查询条件，在最后拼接(OR (A AND B)查询)
                                            String conditionSQL =  " AND " + field + " = \'" + paramValue + "\'";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR " + field + " = \'" + paramValue + "\'";
                                                orAndCondition.add(orAndConditionSQL);
                                            } else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        } else {
                                            String[] fields = field.split(",");
                                            String orSQL = "";
                                            for(int i=0; i<fields.length; i++) {
                                                if(i == 0) {
                                                    orSQL = orSQL + fields[i] + " = \'" + paramValue + "\'";
                                                } else {
                                                    orSQL = orSQL + " OR " + fields[i] + " = \'" + paramValue + "\'";
                                                }
                                            }

                                            String conditionSQL = " AND (" + orSQL + ") ";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR (" + orSQL + ") ";
                                                orAndCondition.add(orAndConditionSQL);
                                            }  else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        }
                                    }
                                } else {
                                    // 模糊查询
                                    paramValue = "%" + paramValue + "%";
                                    conditionMap.put(aliasField, "\'" + paramValue + "\'");
                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        if(!field.contains(",") || (field.contains("(") && field.contains(")"))) {
                                            String conditionSQL = " AND " + field + " LIKE \'" + paramValue + "\'";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR " + field + " LIKE \'" + paramValue + "\'";
                                                orAndCondition.add(orAndConditionSQL);
                                            }  else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        } else {
                                            String[] fields = field.split(",");
                                            String orSQL = "";
                                            for(int i=0; i<fields.length; i++) {
                                                if(i == 0) {
                                                    orSQL = orSQL + fields[i] + " LIKE \'" + paramValue + "\'";
                                                } else {
                                                    orSQL = orSQL + " OR " + fields[i] + " LIKE \'" + paramValue + "\'";
                                                }
                                            }

                                            String conditionSQL = " AND (" + orSQL + ") ";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR (" + orSQL + ") ";
                                                orAndCondition.add(orAndConditionSQL);
                                            }  else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        }
                                    }
                                }
                            } else {
                                // 如果参数值中包含逗号，则使用in查询
                                if (paramValue.contains(",")) {
                                    String finalData = "";
                                    if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                                        String[] values = paramValue.split(",");
                                        for(String data : values) {
                                            String tmpData = "\'" + data + "\',";
                                            finalData = finalData + tmpData;
                                        }

                                        if(finalData.endsWith(",")) {
                                            finalData = finalData.substring(0, finalData.length() - 1);
                                        }
                                    } else {
                                        finalData = paramValue;
                                    }
                                    conditionMap.put(aliasField, finalData);
                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        if(!field.contains(",") || (field.contains("(") && field.contains(")"))) {
                                            String conditionSQL = " AND " + field + " IN ( " + finalData + " ) ";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR " + field + " IN ( " + finalData + " ) ";
                                                orAndCondition.add(orAndConditionSQL);
                                            }  else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        } else {
                                            String[] fields = field.split(",");
                                            String orSQL = "";
                                            for(int i=0; i<fields.length; i++) {
                                                if(i == 0) {
                                                    orSQL = orSQL + fields[i] + " IN ( " + finalData + " ) ";
                                                } else {
                                                    orSQL = orSQL + " OR " + fields[i] + " IN ( " + finalData + " ) ";
                                                }
                                            }

                                            String conditionSQL = " AND (" + orSQL + ") ";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR (" + orSQL + ") ";
                                                orAndCondition.add(orAndConditionSQL);
                                            }  else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        }
                                    }
                                } else {
                                    conditionMap.put(aliasField, "\'" + paramValue + "\'");
                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        if(!field.contains(",") || (field.contains("(") && field.contains(")"))) {
                                            // 如果数据库字段类型为日期类型，则查询时需转换为字符串类型
                                            if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                                String dateField = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
                                                whereSQL = whereSQL + " AND " + dateField + " = '" + paramValue + "\'";
                                            } else {
                                                whereSQL = whereSQL + " AND " + field + " = \'" + paramValue + "\'";
                                            }
                                        } else {
                                            String[] fields = field.split(",");
                                            String orSQL = "";
                                            for(int i=0; i<fields.length; i++) {
                                                if(i == 0) {
                                                    // 如果数据库字段类型为日期类型，则查询时需转换为字符串类型
                                                    if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                                        String dateField = dbFunction.dateToCharFunction(fields[i], rptColumn.getFormatter());
                                                        orSQL = orSQL + dateField + " = '" + paramValue + "\'";
                                                    } else {
                                                        orSQL = orSQL + fields[i] + " = \'" + paramValue + "\'";
                                                    }
                                                } else {
                                                    if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                                        String dateField = dbFunction.dateToCharFunction(fields[i], rptColumn.getFormatter());
                                                        orSQL = orSQL + " OR " + dateField + " = '" + paramValue + "\'";
                                                    } else {
                                                        orSQL = orSQL + " OR " + fields[i] + " = \'" + paramValue + "\'";
                                                    }
                                                }
                                            }

                                            String conditionSQL = " AND (" + orSQL + ") ";
                                            if(Constants.YES.equals(isOr)) {
                                                orCondition.add(conditionSQL);
                                            } else if(Constants.ORAND.equals(isOr)) {
                                                String orAndConditionSQL =  " OR (" + orSQL + ") ";
                                                orAndCondition.add(orAndConditionSQL);
                                            }  else {
                                                whereSQL = whereSQL + conditionSQL;
                                            }
                                        }
                                    }
                                }
                            }
                        } else {
                            if (Constants.CONDITIONTYPE_DATE_RANGE.equals(conditionType)) {  // 判断查询类型是否是日期区间
                                String dateStart = params.get(aliasField + "_start");
                                String dateEnd = params.get(aliasField + "_end");
                                String orAndConditionSQL = "";

                                if (StringUtils.isNotBlank(dateStart)) {
                                    conditionMap.put(aliasField + "_start", "\'" + dateStart + "\'");

                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        String conditionSQL = "";
                                        if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                            String dateStartField = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
                                            conditionSQL = " AND " + dateStartField + " >= '" + dateStart + "\'";
                                        } else {
                                            conditionSQL = " AND " + field + " >= \'" + dateStart + "\'";
                                        }

                                        if(Constants.YES.equals(isOr)) {
                                            orCondition.add(conditionSQL);
                                        } else if(Constants.ORAND.equals(isOr)) {
                                            if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                                String dateStartField = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
                                                orAndConditionSQL = dateStartField + " >= '" + dateStart + "\'";
                                            } else {
                                                orAndConditionSQL =  field + " >= \'" + dateStart + "\'";
                                            }
                                        }  else {
                                            whereSQL = whereSQL + conditionSQL;
                                        }
                                    }
                                }
                                if (StringUtils.isNotBlank(dateEnd)) {
                                    conditionMap.put(aliasField + "_end", "\'" + dateEnd + "\'");

                                    String conditionSQL = "";
                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                            String dateEndField = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
                                            conditionSQL = " AND " + dateEndField + " <= '" + dateEnd + "\'";
                                        } else {
                                            conditionSQL = " AND " + field + " <= \'" + dateEnd + "\'";
                                        }

                                        if(Constants.YES.equals(isOr)) {
                                            orCondition.add(conditionSQL);
                                        } else if(Constants.ORAND.equals(isOr)) {
                                            if(Constants.YES.equals(rptColumn.getIsDateType())) {
                                                String dateEndField = dbFunction.dateToCharFunction(field, rptColumn.getFormatter());
                                                if(StringUtils.isNotBlank(orAndConditionSQL)) {
                                                    orAndConditionSQL = "( " + orAndConditionSQL + " AND " + dateEndField + " <= '" + dateEnd + "\') ";
                                                } else {
                                                    orAndConditionSQL =  dateEndField + " <= '" + dateEnd + "\'";
                                                }
                                            } else {
                                                if(StringUtils.isNotBlank(orAndConditionSQL)) {
                                                    orAndConditionSQL = "( " + orAndConditionSQL + " AND " + field + " <= \'" + dateEnd + "\' )";
                                                } else {
                                                    orAndConditionSQL =  field + " <= \'" + dateEnd + "\'";
                                                }
                                            }
                                        } else {
                                            whereSQL = whereSQL + conditionSQL;
                                        }
                                    }
                                }

                                if(StringUtils.isNotBlank(orAndConditionSQL)) {
                                    orAndConditionSQL = " OR " + orAndConditionSQL;
                                    orAndCondition.add(orAndConditionSQL);
                                }
                            } else if (Constants.CONDITIONTYPE_TREE.equals(conditionType)) {  // 判断查询类型是否是树
                                String treeData = params.get("hid_" + aliasField);
                                if(StringUtils.isNotBlank(treeData)) {
                                    String parseTreeData = "";
                                    if(!treeData.contains("'") && !treeData.contains("\"")) {
                                        String[] treeDatas = treeData.split(",");
                                        for(String data : treeDatas) {
                                            String tmpData = "\'" + data + "\',";
                                            parseTreeData = parseTreeData + tmpData;
                                        }

                                        if(parseTreeData.endsWith(",")) {
                                            parseTreeData = parseTreeData.substring(0, parseTreeData.length() - 1);
                                        }
                                    } else {
                                        parseTreeData = treeData;
                                    }
                                    if (StringUtils.isNotBlank(treeData) && !"''".equals(treeData)) {
                                        conditionMap.put(aliasField, parseTreeData);

                                        if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                            if(!field.contains(",") || (field.contains("(") && field.contains(")"))) {
                                                String conditionSQL = " AND " + field + " IN (" + parseTreeData + ")";
                                                if(Constants.YES.equals(isOr)) {
                                                    orCondition.add(conditionSQL);
                                                } else if(Constants.ORAND.equals(isOr)) {
                                                    String orAndConditionSQL = " OR " + field + " IN (" + parseTreeData + ")";
                                                    orAndCondition.add(orAndConditionSQL);
                                                } else {
                                                    whereSQL = whereSQL + conditionSQL;
                                                }
                                            } else {
                                                String[] fields = field.split(",");
                                                String orSQL = "";
                                                for(int i=0; i<fields.length; i++) {
                                                    if(i == 0) {
                                                        orSQL = orSQL + fields[i] + " IN (" + parseTreeData + ")";
                                                    } else {
                                                        orSQL = orSQL + " OR " + fields[i] + " IN(" + parseTreeData + ")";
                                                    }
                                                }

                                                String conditionSQL = " AND (" + orSQL + ")";
                                                if(Constants.YES.equals(isOr)) {
                                                    orCondition.add(conditionSQL);
                                                } else if(Constants.ORAND.equals(isOr)) {
                                                    String orAndConditionSQL = " OR (" + orSQL + ") ";
                                                    orAndCondition.add(orAndConditionSQL);
                                                } else {
                                                    whereSQL = whereSQL + conditionSQL;
                                                }
                                            }
                                        }
                                    }
                                }
                            } else if(Constants.CONDITIONTYPE_VALUE_RANGE.equals(conditionType)) {  // 判断查询类型是否是值区间
                                String dateStart = params.get(aliasField + "_start");
                                String dateEnd = params.get(aliasField + "_end");
                                String orAndConditionSQL = "";

                                if (StringUtils.isNotBlank(dateStart)) {
                                    conditionMap.put(aliasField + "_start", dateStart);

                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        String conditionSQL = " AND " + field + " >= " + dateStart;
                                        if(Constants.YES.equals(isOr)) {
                                            orCondition.add(conditionSQL);
                                        } else if(Constants.ORAND.equals(isOr)) {
                                            orAndConditionSQL = field + " >= " + dateStart;
                                        } else {
                                            whereSQL = whereSQL + conditionSQL;
                                        }
                                    }
                                }
                                if (StringUtils.isNotBlank(dateEnd)) {
                                    conditionMap.put(aliasField + "_end", dateEnd);

                                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                        String conditionSQL = " AND " + field + " <= " + dateEnd;
                                        if(Constants.YES.equals(isOr)) {
                                            orCondition.add(conditionSQL);
                                        } else if(Constants.ORAND.equals(isOr)) {
                                            if(StringUtils.isNotBlank(orAndConditionSQL)) {
                                                orAndConditionSQL = " ( " + orAndConditionSQL + " AND " + field + " <= " + dateEnd + " ) ";
                                            } else {
                                                orAndConditionSQL = field + " <= " + dateEnd;
                                            }
                                        }  else {
                                            whereSQL = whereSQL + conditionSQL;
                                        }
                                    }
                                }

                                if(StringUtils.isNotBlank(orAndConditionSQL)) {
                                    orAndConditionSQL = " OR " + orAndConditionSQL;
                                    orAndCondition.add(orAndConditionSQL);
                                }
                            } else if(aliasField.contains(",")) {
                                if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                                    String conditionSQL = "";
                                    String[] aliasFields = aliasField.split(",");
                                    for(String columnField : aliasFields) {
                                        String finalParams = "";
                                        String fieldValue = params.get(columnField);
                                        if(StringUtils.isNotBlank(fieldValue) && !Constants.CONSTANTS_UNDEFINED.equalsIgnoreCase(fieldValue)) {
                                            if(fieldValue.contains(",")) {
                                                if(!fieldValue.contains("'") && !fieldValue.contains("\"")) {
                                                    String[] tmpParams = fieldValue.split(",");
                                                    for(String tmpStr : tmpParams) {
                                                        finalParams = finalParams +  "\'" + tmpStr + "\',";
                                                    }
                                                    if(finalParams.endsWith(",")) {
                                                        finalParams = finalParams.substring(0, finalParams.length() - 1);
                                                    }
                                                } else {
                                                    finalParams = fieldValue;
                                                }

                                                if(StringUtils.isBlank(conditionSQL)) {
                                                    conditionSQL = conditionSQL + field + " IN (" + finalParams + " )";
                                                } else {
                                                    conditionSQL = conditionSQL + " OR " + field + " IN (" + finalParams + " )";
                                                }
                                            } else {
                                                if(Constants.CONSTANTS_NULL.equalsIgnoreCase(fieldValue)) {
                                                    if(StringUtils.isBlank(conditionSQL)) {
                                                        conditionSQL = conditionSQL + field + " IS NULL ";
                                                    } else {
                                                        conditionSQL = conditionSQL + " OR " + field + " IS NULL ";
                                                    }
                                                } else {
                                                    if(!fieldValue.contains("'") && !fieldValue.contains("\"")) {
                                                        finalParams = "\'" + fieldValue + "\'";
                                                    } else {
                                                        finalParams = fieldValue;
                                                    }

                                                    if(StringUtils.isBlank(conditionSQL)) {
                                                        conditionSQL = conditionSQL + field + " = " + finalParams;
                                                    } else {
                                                        conditionSQL = conditionSQL + " OR " + field + " = " + finalParams;
                                                    }
                                                }
                                            }
                                        }
                                    }

                                    conditionSQL = " AND ( " + conditionSQL + " )";
                                    if(Constants.YES.equals(isOr)) {
                                        orCondition.add(conditionSQL);
                                    } else if(Constants.ORAND.equals(isOr)) {
                                        String orAndConditionSQL = " OR (" + conditionSQL + ") ";
                                        orAndCondition.add(orAndConditionSQL);
                                    } else {
                                        whereSQL = whereSQL + conditionSQL;
                                    }
                                }
                            }
                        }
                    }
                }
            }

            // 拼接初始化查询数据(当查询条件不是来源于页面form表单，查询条件由上一个页面下钻而来)
            List<BaseRptColumn> rptColumns = baseRpt.getRptColumns();
            List<BaseRptColumn> hiddenColumns = baseRpt.getHiddenRptColumns();
            List<String> orConditionList = new ArrayList<String>();
            for(int i=0; i<initParams.length; i++) {
                boolean flag = true;
                boolean conditionFlag = false;
                String aliasField = initParams[i];
                String paramValue = params.get(aliasField);  // 参数值
                String field = "";
                String isOr = "";
                if(rptColumns != null && rptColumns.size() > 0) {
                    for(BaseRptColumn rptColumn : rptColumns) {
                        String columnField = rptColumn.getAliasField();
                        String conditionLvl = rptColumn.getConditionLevel();
                        String isCondition = rptColumn.getIsCondition();
                        if(!Constants.YES.equals(isCondition)) {  // 查询条件列已经拼接，在此需要过滤，防止重复
                            if(aliasField.equals(columnField)) {
                                conditionFlag = isConditionLvl(conditionLvl, needLvl);
                                if(conditionFlag) {
                                    field = rptColumn.getField();
                                    isOr = rptColumn.getIsOrOperator();
                                    whereSQL = concateInitWhereSQL(paramValue, conditionFlag, field, columnField, isOr, orCondition, orAndCondition, whereSQL, conditionMap);
                                    break;
                                }
                            } else {
                                if(columnField.contains(",")) {
                                    conditionFlag = isConditionLvl(conditionLvl, needLvl);
                                    if(conditionFlag && columnField.contains(aliasField) && !orConditionList.contains(aliasField)) {
                                        String[] filds = columnField.split(",");
                                        field = rptColumn.getField();
                                        isOr = rptColumn.getIsOrOperator();
                                        for(String orField : filds) {
                                            orConditionList.add(orField);
                                        }
                                        whereSQL = concateInitWhereSQL(columnField, conditionFlag, field, isOr, orCondition, orAndCondition, whereSQL, conditionMap, params);
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
                if(flag) {
                    if(hiddenColumns != null && hiddenColumns.size() > 0) {
                        for(BaseRptColumn rptColumn : hiddenColumns) {
                            String columnField = rptColumn.getAliasField();
                            String conditionLvl = rptColumn.getConditionLevel();
                            String isCondition = rptColumn.getIsCondition();
                            if(!Constants.YES.equals(isCondition)) {
                                if(aliasField.equals(columnField)) {  // 查询条件列已经拼接，在此需要过滤，防止重复
                                    conditionFlag = isConditionLvl(conditionLvl, needLvl);
                                    if(conditionFlag) {
                                        field = rptColumn.getField();
                                        isOr = rptColumn.getIsOrOperator();
                                        whereSQL = concateInitWhereSQL(paramValue, conditionFlag, field, columnField, isOr, orCondition, orAndCondition, whereSQL, conditionMap);
                                        //break;
                                    }
                                } else {
                                    if(columnField.contains(",")) {
                                        conditionFlag = isConditionLvl(conditionLvl, needLvl);
                                        if(conditionFlag && columnField.contains(aliasField) && !orConditionList.contains(aliasField)) {
                                            String[] filds = columnField.split(",");
                                            field = rptColumn.getField();
                                            isOr = rptColumn.getIsOrOperator();
                                            for(String orField : filds) {
                                                orConditionList.add(orField);
                                            }
                                            whereSQL = concateInitWhereSQL(columnField, conditionFlag, field, isOr, orCondition, orAndCondition, whereSQL, conditionMap, params);
                                            //break;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                // todo
            }
        }

        if(orCondition != null && orCondition.size() > 0) {
            String orSQL = "";
            for(String conditionSQL : orCondition) {
                orSQL = orSQL + conditionSQL;
            }
            orSQL = orSQL.trim();
            if(orSQL.startsWith("AND")) {
                orSQL = orSQL.substring(3);
            }
            whereSQL = whereSQL + " OR ( " + orSQL + " )";
        }

        if(orAndCondition != null && orAndCondition.size() > 0) {
            String orSQL = "";
            for(String conditionSQL : orAndCondition) {
                orSQL = orSQL + conditionSQL;
            }
            orSQL = orSQL.trim();
            if(orSQL.startsWith("OR")) {
                orSQL = orSQL.substring(2);
            }
            whereSQL = whereSQL + " AND ( " + orSQL + " )";
        }

        // 因为order为报表工具的关键字, 所以aliasField的值不能为order，在此用作key可以避免key被覆盖
        conditionMap.put(Constants.WHEREKEY, whereSQL);
        return conditionMap;
    }

    /**
     * 拼接下钻查询条件
     * @param paramValue
     * @param conditionFlag
     * @param field
     * @param isOr
     * @param orCondition
     * @param whereSQL
     * @return
     */
    private String concateInitWhereSQL(String paramValue, boolean conditionFlag, String field, String aliasField, String isOr, List<String> orCondition,List<String> orAndCondition, String whereSQL, Map<String, String> conditionMap) {
        if(StringUtils.isNotBlank(paramValue) && !Constants.CONSTANTS_UNDEFINED.equalsIgnoreCase(paramValue) && conditionFlag) {
            if(!field.contains(",") || (field.contains("(") && field.contains(")"))) {
                String finalParams = "";
                if(paramValue.contains(",")) {
                    if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                        String[] tmpParams = paramValue.split(",");
                        for(String tmpStr : tmpParams) {
                            finalParams = finalParams +  "\'" + tmpStr + "\',";
                        }
                        if(finalParams.endsWith(",")) {
                            finalParams = finalParams.substring(0, finalParams.length() - 1);
                        }
                    } else {
                        finalParams = paramValue;
                    }

                    conditionMap.put(aliasField, finalParams);
                    String conditionSQL = " AND " + field + " IN (" + finalParams + " ) ";
                    if(Constants.YES.equals(isOr)) {
                        orCondition.add(conditionSQL);
                    } else if(Constants.ORAND.equals(isOr)) {
                        String orAndConditionSQL = " OR " + field + " IN ( " + finalParams + " ) ";
                        orAndCondition.add(orAndConditionSQL);
                    } else {
                        whereSQL = whereSQL + conditionSQL;
                    }
                } else {
                    if(Constants.CONSTANTS_NULL.equals(paramValue)) {
                        String conditionSQL = " AND " + field + " IS NULL ";
                        if(Constants.YES.equals(isOr)) {
                            orCondition.add(conditionSQL);
                        } else if(Constants.ORAND.equals(isOr)) {
                            String orAndConditionSQL = " OR " + field + " IS NULL ";
                            orAndCondition.add(orAndConditionSQL);
                        }  else {
                            whereSQL = whereSQL + conditionSQL;
                        }
                    } else {
                        if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                            finalParams = "\'" + paramValue + "\'";
                        } else {
                            finalParams = paramValue;
                        }

                        conditionMap.put(aliasField, finalParams);
                        String conditionSQL = " AND " + field + " = " + finalParams;
                        if(Constants.YES.equals(isOr)) {
                            orCondition.add(conditionSQL);
                        } else if(Constants.ORAND.equals(isOr)) {
                            String orAndConditionSQL = " OR " + field + " = " + finalParams;
                            orAndCondition.add(orAndConditionSQL);
                        } else {
                            whereSQL = whereSQL + conditionSQL;
                        }
                    }
                }
            } else {
                String[] fields = field.split(",");
                String orSQL = "";
                String finalParams = "";
                if(paramValue.contains(",")) {
                    if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                        String[] tmpParams = paramValue.split(",");
                        for(String tmpStr : tmpParams) {
                            finalParams = finalParams +  "\'" + tmpStr + "\',";
                        }
                        if(finalParams.endsWith(",")) {
                            finalParams = finalParams.substring(0, finalParams.length() - 1);
                        }
                    } else {
                        finalParams = paramValue;
                    }

                    conditionMap.put(aliasField, finalParams);
                    for(int k=0; k<fields.length; k++) {
                        if(k == 0) {
                            orSQL = orSQL + fields[k] + " IN ( " + finalParams + " )";
                        } else {
                            orSQL = orSQL + " OR " + fields[k] + " IN (" + paramValue + " )";
                        }
                    }

                    String conditionSQL = " AND (" + orSQL + ") ";
                    if(Constants.YES.equals(isOr)) {
                        orCondition.add(conditionSQL);
                    } else if(Constants.ORAND.equals(isOr)) {
                        String orAndConditionSQL = " OR (" + orSQL + ") ";
                        orAndCondition.add(orAndConditionSQL);
                    } else {
                        whereSQL = whereSQL + conditionSQL;
                    }
                } else {
                    if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                        finalParams = "\'" + paramValue + "\'";
                    } else {
                        finalParams = paramValue;
                    }

                    conditionMap.put(aliasField, finalParams);
                    for(int k=0; k<fields.length; k++) {
                        if(k == 0) {
                            if(Constants.CONSTANTS_NULL.equals(paramValue)) {
                                orSQL = orSQL + fields[k] + " IS NULL ";
                            } else {
                                orSQL = orSQL + fields[k] + " = " + finalParams;
                            }
                        } else {
                            if(Constants.CONSTANTS_NULL.equals(paramValue)) {
                                orSQL = orSQL + " OR " + fields[k] + " IS NULL ";
                            } else {
                                orSQL = orSQL + " OR " + fields[k] + " = " + finalParams;
                            }
                        }
                    }

                    String conditionSQL = " AND (" + orSQL + ") ";
                    if(Constants.YES.equals(isOr)) {
                        orCondition.add(conditionSQL);
                    } else if(Constants.ORAND.equals(isOr)) {
                        String orAndConditionSQL = " OR (" + orSQL + ") ";
                        orAndCondition.add(orAndConditionSQL);
                    }  else {
                        whereSQL = whereSQL + conditionSQL;
                    }
                }
            }
        }

        return whereSQL;
    }

    /**
     * 拼接下钻查询条件
     * @param aliasField
     * @param conditionFlag
     * @param field
     * @param isOr
     * @param orCondition
     * @param whereSQL
     * @return
     */
    private String concateInitWhereSQL(String aliasField, boolean conditionFlag, String field, String isOr, List<String> orCondition, List<String> orAndCondition, String whereSQL, Map<String, String> conditionMap, Map<String, String> params) {
        String[] aliasFields = aliasField.split(",");
        String conditionSQL = "";
        for(String columnField : aliasFields) {
            String finalParams = "";
            String paramValue = params.get(columnField);
            if(StringUtils.isNotBlank(paramValue) && !Constants.CONSTANTS_UNDEFINED.equalsIgnoreCase(paramValue) && conditionFlag) {
                if(paramValue.contains(",")) {
                    if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                        String[] tmpParams = paramValue.split(",");
                        for(String tmpStr : tmpParams) {
                            finalParams = finalParams +  "\'" + tmpStr + "\',";
                        }
                        if(finalParams.endsWith(",")) {
                            finalParams = finalParams.substring(0, finalParams.length() - 1);
                        }
                    } else {
                        finalParams = paramValue;
                    }

                    conditionMap.put(aliasField, finalParams);
                    if(StringUtils.isBlank(conditionSQL)) {
                        conditionSQL = conditionSQL + field + " IN (" + finalParams + " )";
                    } else {
                        conditionSQL = conditionSQL + " OR " + field + " IN (" + finalParams + " )";
                    }
                } else {
                    if(Constants.CONSTANTS_NULL.equalsIgnoreCase(paramValue)) {
                        if(StringUtils.isBlank(conditionSQL)) {
                            conditionSQL = conditionSQL + field + " IS NULL ";
                        } else {
                            conditionSQL = conditionSQL + " OR " + field + " IS NULL ";
                        }
                    } else {
                        if(!paramValue.contains("'") && !paramValue.contains("\"")) {
                            finalParams = "\'" + paramValue + "\'";
                        } else {
                            finalParams = paramValue;
                        }

                        conditionMap.put(aliasField, finalParams);
                        if(StringUtils.isBlank(conditionSQL)) {
                            conditionSQL = conditionSQL + field + " = " + finalParams;
                        } else {
                            conditionSQL = conditionSQL + " OR " + field + " = " + finalParams;
                        }
                    }
                }
            }
        }

        conditionSQL = " AND ( " + conditionSQL + " )";
        if(Constants.YES.equals(isOr)) {
            orCondition.add(conditionSQL);
        } else if(Constants.ORAND.equals(isOr)) {
            String orAndConditionSQL = " OR (" + conditionSQL + ") ";
            orAndCondition.add(orAndConditionSQL);
        }  else {
            whereSQL = whereSQL + conditionSQL;
        }

        return whereSQL;
    }

    /**
     * 拼接最外层查询条件
     * @param baseRpt
     * @param params
     * @param needLvl
     * @return
     */
    private String concateOuterWhereSQL(BaseRpt baseRpt, Map<String, String> params, String needLvl, IDBFunction dbFunction) {
        String resultSQL = "";

        // 报表查询条件可分为页面传入的查询条件及页面跳转传入的初始化查询条件
        if(params != null && params.size() > 0) {
            List<BaseRptColumn> conditionCol = baseRpt.getConditionColumns();  // 报表查询列
            String initParam = baseRpt.getInitCondition();  // 报表初始化查询条件
            String[] initParams = initParam.split(",");
            // 查询条件来源于页面form表单
            if (conditionCol != null && conditionCol.size() > 0) {
                for (BaseRptColumn rptColumn : conditionCol) {  // 拼接查询列数据
                    String isSQLColumn = rptColumn.getIsSQLColumn();
                    if(Constants.YES.equals(isSQLColumn) || StringUtils.isBlank(isSQLColumn)) {
                        String conditionLvl = rptColumn.getConditionLevel();

                        if(isConditionLvl(conditionLvl, needLvl)) {
                            String field = rptColumn.getField();
                            String aliasField = rptColumn.getAliasField();
                            String conditionType = rptColumn.getConditionType();  // 查询条件类型
                            String paramValue = params.get(aliasField);  // 参数值
                            if (StringUtils.isNotBlank(paramValue)) {
                                if (Constants.CONDITIONTYPE_INPUT.equals(conditionType)) { // 如果查询类型为输入框，判断是否是模糊查询
                                        resultSQL = resultSQL + " AND \"" + aliasField + "\" = " + paramValue;
                                }
                            } else {
                                if(Constants.CONDITIONTYPE_VALUE_RANGE.equals(conditionType)) {  // 判断查询类型是否是值区间
                                    String dateStart = params.get(aliasField + "_start");
                                    String dateEnd = params.get(aliasField + "_end");

                                    if(StringUtils.isNotBlank(dateStart)) {
                                        resultSQL = resultSQL + " AND \"" + aliasField + "\" >=" + dateStart;
                                    }
                                    if(StringUtils.isNotBlank(dateEnd)) {
                                        resultSQL = resultSQL + " AND \"" + aliasField + "\" <=" + dateEnd;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if(StringUtils.isNotBlank(resultSQL)) {
            resultSQL = " WHERE 1 = 1 " + resultSQL;
        }
        return resultSQL;
    }

    /**
     * 判断查询条件是否在指定层级
     * @param conditionLvl 查询条件层级
     * @param needLvl 指定层级
     * @return
     */
    private boolean isConditionLvl(String conditionLvl, String needLvl) {
        boolean flag = false;
        if(StringUtils.isBlank(conditionLvl)) {
            conditionLvl = Constants.CONDITION_LVL_MID;
        }

        if(conditionLvl.equals(needLvl)) {
            flag = true;
        }

        return flag;
    }
}
